package servlet.admin;

import dao.BaseDao;
import entity.DateTime;
import entity.Util;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * LibraryData
 *
 * @author 谭文斌
 * @since 2023/9/4
 */
@WebServlet("/admin/libraryData")
public class LibraryData extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("application/json; charset:utf8");
        // 准备参数
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String sql = "";
        JSONObject jsonObject = new JSONObject();
        JSONArray jsonData = new JSONArray();
        JSONArray jsonDays = new JSONArray();
        // 返回参数
        int code = 1;
        String msg = "error";
        int count = 0;
        PrintWriter out = resp.getWriter();

        // 开始查询
        try {
            connection = BaseDao.getConnection();
            int i = 30;
            // 获取30天
            while(i!=0) {
                i--;
                sql = "select count(*) as count from borrow_books where date_format(borrow_date,'%Y-%m-%d')=? order by id desc";
                String date = DateTime.showDate(-i); // 设置日期
                String md = DateTime.showMD(-i);
                pstmt = connection.prepareStatement(sql);
                pstmt.setString(1,date);
                resultSet = pstmt.executeQuery();
                while(resultSet.next()) {
                    jsonData.add(resultSet.getString("count"));
                    jsonDays.add(md);
                }
            }
            jsonObject.put("data", jsonData);
            jsonObject.put("days", jsonDays);
            if(!jsonObject.isEmpty()) {
                code = 0;
                msg =  "查询成功";
            }else {
                msg = "数据为空";
            }
        } catch (SQLException e) {
            msg = "sql错误";
        }finally {
            BaseDao.closeAll(resultSet, pstmt, connection);
        }
        out.print( Util.jsonResponse(code, msg, jsonObject.toString()) );
    }
}
